﻿<html DIR="LTR" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ddue="http://ddue.schemas.microsoft.com/authoring/2003/5" xmlns:MSHelp="http://msdn.microsoft.com/mshelp">
  <head>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8" />
    <META NAME="save" CONTENT="history" />
    <title>Readme_Execute Process Package Sample</title>
    
    
    
    <style TYPE="text/css">
body
{
background: #FFFFFF;
color: #000000;
font-family:    Verdana;
font-size: medium;
font-style: normal;
font-weight: normal;
margin-top: 0;
margin-bottom:  0;
margin-left:    0;
margin-right:   0;
width:  100%;
}

div.#mainSection
{
font-size: 70%;
width: 100%;
padding-left:    10;
margin-right: 10;
}

div.#mainBody
{
font-size: 90%;
margin-top: 10;
padding-bottom: 20;
}

div.#header
{
background-color: #D2D2D2;
padding-top:    0;
padding-bottom: 0;
padding-left:   10;
padding-right:  0;
width:          100%;
}

div.#header table
{
border-bottom-color: #C8CDDE;
border-bottom-style: solid;
border-bottom-width: 1;
width:  100%;
}

span.#runningHeaderText
{
color: #003399;
font-size: 90%;
}

span.#nsrTitle
{
/*    color: #003399;*/
font-size: 120%;
font-weight: 600;
}

div.#header table td
{
color: #000000;
font-size: 70%;
margin-top: 0;
margin-bottom:  0;
padding-right: 20;
}

div.#header table tr.#headerTableRow3 td
{
padding-bottom: 2;
padding-top: 5;
}

div.#header table.#bottomTable
{
border-top-color: #FFFFFF;
border-top-style: solid;
border-top-width: 1;
text-align: left;
}

div.#footer
{
font-size: 90%;
margin-top: 0;
margin-bottom:  0;
margin-left:    -5;
margin-right:   0;
padding-top:    2;
padding-bottom: 2;
padding-left:   0;
padding-right:  0;
width:  100%;
}

hr.#footerHR
{
border-bottom-color: #EEEEFF;
border-bottom-style: solid;
border-bottom-width: 1;
border-top-color: C8CDDE;
border-top-style: solid;
border-top-width: 1;
height: 3;
color: #D2D2D2;
}

div.section
{
padding-top:    2;
padding-bottom: 2;
padding-right:  15;
width:  100%;
}

.heading
{
color:          #000000;
font-weight:    bold;
margin-top:     18;
margin-bottom:  8;
}

h1.heading
{
color: #000000;
font-size:  150%;
}

.subHeading
{
color:          #000000;
font-weight:    bold;
font-size:      150%;
margin-bottom:  4;
}

h2.subHeading
{
color:          #000000;
font-weight:    bold;
font-size:      130%;
}
h3.subHeading
{
color:  #000000;
font-size: 125%;
font-weight: bold;
}

h4.subHeading
{
color: #000000;
font-size: 110%;
font-weight: bold;
}

h4.procedureHeading
{
color: #000080;
font-size: 110%;
font-weight: bold;
}

h5.subHeading
{
color: #000000;
font-size: 100%;
font-weight: bold;
}

img
{
padding-bottom: 10;
}

img.toggle
{
border: 0;
margin-right: 5;
padding-bottom: 10;
}

img.copyCodeImage
{
border: 0;
margin: 1;
margin-right: 3;
padding-bottom: 10;
}

img.downloadCodeImage
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.viewCodeImage
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.note
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.#membersOptionsFilterImage
{
border: 0;
margin-left: 10;
vertical-align: middle;
padding-bottom: 10;
}

img.#toggleAllImage
{
margin-left: 4;
vertical-align: middle;
padding-bottom: 10;
}

div.#mainSection table
{
border: 0;
font-size: 100%;
width:  100%;
margin-top: 5px;
margin-bottom: 15px;
}

div.#mainSection table tr
{
vertical-align: top;
}

div.#mainSection table th
{
text-align: left;
background: #D8D8D8;
border-bottom-color: #D8D8D8;
border-bottom-style: solid;
border-bottom-width: 1;
color: #000000;
padding-left: 5;
padding-right: 5;
}

div.#mainSection table td
{
background: #F2F2F2;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
}

div.#mainSection table td.imageCell
{
white-space: nowrap;
}

div.code
{
width: 98%;
}

div.code table
{
border: 0;
font-size: 95%;
margin-bottom: 5;
width: 100%
}

div.code table th
{
text-align: left;
background: #D8D8D8;
border-bottom-color: #D8D8D8;
border-bottom-style: solid;
border-bottom-width: 1;
color: #000000;
font-weight: bold;
padding-left: 5;
padding-right: 5;
}

div.code table td
{
background: #CCCCCC;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
padding-top: 5;
}

div.alert
{
margin-left: 10;
width: 98%;
}

div.alert table
{
border: 1;
font-size: 100%;
width:  100%;
border: solid 1 #DEDFEF;
}

div.alert table th
{
text-align: left;
background: #D8D8D8;
border-bottom-width: 0;
color: #000000;
padding-left: 5;
padding-right: 5;
border: solid 1 #DEDFEF;
}

div.alert table td
{
background: #FFFFFF;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
border: solid 1 #DEDFEF;
}

span.copyCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
float: right;
display: inline;
text-align: right;
}

.downloadCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
}

.viewCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
}

div.code pre
{
font-family:    Monospace, Courier New, Courier;
font-size: 105%;
color:  #000000;
}

code
{
font-family:    Monospace, Courier New, Courier;
font-size: 105%;
color:  #000000;
}

dl
{
margin-top: 0;
padding-left:   1;
}

dd
{
margin-bottom:  0;
margin-left:    0;
padding-left:   20;
}

dd p
{
margin-top: 5;
}

ul
{
margin-left: 17;
list-style-type: disc;
}

ul ul
{
margin-bottom: 4;
margin-left: 17;
margin-top: 3;
list-style-type: disc;
}

ol
{
margin-left: 24;
list-style-type: decimal;
}

ol ol
{
margin-left: 24;
margin-top: 3;
list-style-type: lower-alpha;
}

li
{
margin-top: 0;
margin-bottom: 0;
padding-bottom: 0;
padding-top: 0;
margin-left: 5;
}

p
{
margin-bottom: 15;
}

.tip
{
color:  #0000FF;
font-style: italic;
cursor:hand;
text-decoration:underline;
}

.math
{
font-family: Times New Roman;
font-size: 125%
}
.sourceCodeList
{
font-family: Verdana;
font-size: 90%;
}

pre.viewCode
{
width: 100%;
overflow: auto;
}

li:hover table, li.over table
{
background-color: #C0C0C0;
}

li:hover ul, li.over ul
{
background-color: #d2d2d2;
border: 1px solid #000;
display: block;
}
            </style>
  </head>
  <body>
    <!--Topic built:08/05/2008 01:28:33-->

    
    
    
    
    
    
    
    
    
    <div id="header">
      <table width="100%" id="topTable"><tr>
          <td align="left">
            <span id="nsrTitle">Readme_Execute Process Package Sample</span>
          </td>
          <td align="right">
            <span id="headfb" class="feedbackhead">
            </span>
          </td>
        </tr></table>
      
      
      
    </div>
    <div id="mainSection">
      <div id="mainBody"><p> 08/05/2008 01:28:33</p>
        
        <font color="DarkGray"> </font><p /> 
        <span id="changeHistory">
        </span>
    <p>
      This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
    </p>
    <p>This sample works with the SQL Server 2005 version of the <b>AdventureWorks</b> OLTP database. To install this database, see <a href="http://go.microsoft.com/fwlink/?LinkId=122077" alt="blocked::http://go.microsoft.com/fwlink/?LinkId=122077"><linkText xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">Sample Databases for Microsoft SQL Server 2008</linkText></a>. </p>
    <p>The Execute Process sample is a package that runs an executable from within the package. In this case, the sample package includes the following Integration Services components:</p>
    <ul><li>
        An Execute Process task that runs the <b>Expand</b> utility (expand.exe). The <b>Expand</b> utility decompresses a CAB file that contains an Excel spreadsheet that is subsequently used as source data for the rest of the package.<br />
      </li><li>
        An Execute SQL task uses a stored procedure to create the <b>Execute_Process_Dest</b> table the first time that the package runs. (The stored procedure that creates the table is stored in a separate file.) The Execute SQL task then truncates <b>Execute_Process_Dest</b> table when the package is run again.<br />
      </li><li>
        An OLE DB source reads the data from the Excel spreadsheet, and an OLE DB destination writes the data from the spreadsheet to <b>Execute_Process_Dest</b> table.<br />
      </li><li>
        A second Execute SQL task queries the <b>Execute_Process_Dest</b> table and stores the table rows in a variable of the <b>Object</b> data type.<br />
      </li><li>
        A Foreach Loop container extracts the table row values, that stored in the variable of the <b>Object</b> data type, into separate variables. The container has a Script task that writes the values of the separate variables to a text file.<br />
      </li></ul>
    <p>If you run the sample on a non-English version of Windows, you may have to substitute the localized name of the Program Files folder to open or run the sample.</p>
    <div class="alert"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left">Important: </th></tr><tr><td>
      Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.<p />
    </td></tr></table><p /></div>
  <h1 class="heading">Requirements</h1><div id="requirementsSection" class="section">
    <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">Running this sample package requires the following:</p>
      <ul xmlns=""><li>
          The sample package and data files that it uses must be installed on the local hard disk drive. <br />
        </li><li>
          You must have installed and have administrative permissions on the <b>AdventureWorks</b> OLTP database. <br />
        </li><li>
          If you intend only to run the sample package from the command line, you must install Integration Services. <br />
        </li><li>
          If you intend to open the package in SSIS Designer and run the sample package, you must install Business Intelligence Development Studio. <br />
        </li><li>
          You may have to modify the Execute Process Task to provide the correct path of the <b>Expand</b> utility. Depending on the operating system, the <b>Expand</b> utility may be located in a different directory than that specified in the sample package. To modify the Execute Process task, right-click the task in the <b>Control Flow</b> tab and select <b>Edit</b>. In the <b>Execute Process Task Editor</b>, set the path of the <b>Executable</b> property to the folder where the <b>Expand</b> utility is located. <br />
        </li></ul>
      <p xmlns="">For more information about how to install samples, see the topic, "Installing Sample Integration Services Packages", in SQL Server Books Online. </p>
    </content>
  </div><h1 class="heading">Location of the Sample Package</h1><div id="sectionSection0" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">If the samples were installed to the default installation location, the Execute Process sample package is located in the following folder: </p>
      <p xmlns="">
        C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\ExecuteProcess Sample\ExecuteProcess\</p>
      <p xmlns="">The following files are required to run this sample package.</p>
      <table width="100%" cellspacing="0" cellpadding="0" border="1" style="background-color: #CCCCCC;" xmlns=""><tr>
            <th>
              File
            </th>
            <th>
              Description
            </th>
          </tr><tr>
          <td>
            <p>UsingExecuteProcess.dtsx</p>
          </td>
          <td>
            <p>The sample package.</p>
          </td>
        </tr><tr>
          <td>
            <p>Customers.cab</p>
          </td>
          <td>
            <p>The CAB file that contains the compressed Excel spreadsheet used as the data source in the package.</p>
          </td>
        </tr><tr>
          <td>
            <p>Create_Execute_Process_Dest.sql</p>
          </td>
          <td>
            <p>The stored procedure that creates a destination table in the <b>AdventureWorks</b> database.</p>
          </td>
        </tr><tr>
          <td>
            <p>CheckQueryResults.txt</p>
          </td>
          <td>
            <p>The text file that contains the results of the query that the second Execute SQL task runs.</p>
          </td>
        </tr></table>
    </content></div><h1 class="heading">Running the Sample</h1><div id="sectionSection1" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">The package can be run from the command line by using the <b>dtexec</b> utility, or can be run in Business Intelligence Development Studio.</p>
      <p xmlns="">If you are using a non-English version of Windows, you may have to update the <b>ConnectionString</b> property of any file connection managers used in the package to run the sample package successfully. You should verify that the path used in the connection manager is valid on your computer, and if you need to, modify the path so that it uses the localized name of the Program Files folder.</p>
      <div class="alert" xmlns=""><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left">Important: </th></tr><tr><td>
        This sample uses the Microsoft Jet 4.0 OLE DB provider, for which there is no 64-bit version. The package fails on a 64-bit computer.<p />
      </td></tr></table><p /></div>
      <p xmlns="">For this sample, you may have to update "Program Files" in the <b>ConnectionString</b> property for the Check Query Results, Create_Execute_Process_Dest.sql, and customers connection managers.</p>
      <h4 class="procedureHeading" xmlns="">To run the package by using dtexec</h4><div id="procedureSectionEHBHBHA" class="section" xmlns=""><ol><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Open a Command Prompt window.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Change the directory to C: Program Files\Microsoft SQL Server\100\DTS\Binn, the location of <b>dtexec</b>.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Type the following command:</p>
              <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>dtexec /f "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\ExecuteProcess Sample\ExecuteProcess\UsingExecuteProcess.dtsx"</pre></td></tr></table></span></div>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Press <b>Enter</b>.</p>
            </content>
          </li></ol></div>
      <p xmlns="">For more information about how to run the package by using the <b>dtexec</b> utility, see the topic, "dtexec Utility", in SQL Server Books Online. </p>
      <h4 class="procedureHeading" xmlns="">To run the package in Business Intelligence Development Studio</h4><div id="procedureSectionEDBHBHA" class="section" xmlns=""><ol><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Open Business Intelligence Development Studio.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">On the <b>File</b> menu, point to <b>Open</b>, and then click <b>Project/Solution</b>.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Locate the <b>ExecuteProces</b>s<b> Sample</b> folder, and then double-click the file named ExecuteProcess.sln.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">In <b>Solution Explorer</b>, right-click ExecuteProcess.dtsx in the <b>SSIS Packages</b> folder, and then click <b>Execute Package</b>. </p>
            </content>
          </li></ol></div>
      <p xmlns="">For more information about how to run the package in Business Intelligence Development Studio, see the topic, "Running Packages", in SQL Server Books Online.</p>
    </content></div><h1 class="heading">Components in Sample</h1><div id="sectionSection2" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">The following table lists the tasks, sources, and destinations that are used in the sample.</p>
      <table width="100%" cellspacing="0" cellpadding="0" border="1" style="background-color: #CCCCCC;" xmlns=""><tr>
            <th>
              Element
            </th>
            <th>
              Purpose
            </th>
          </tr><tr>
          <td>
            <p>Execute SQL task</p>
          </td>
          <td>
            <p>Runs the stored procedure in the file, Create_Execute_Process_Dest.sql, to create the destination table in the <b>AdventureWorks</b> database. The table is created the first time that you run the package. If you run the package again, the table is truncated.</p>
            <p>For its return code, the stored procedure returns a value of 0 or 1. A value of 0 indicates that the stored procedure ran successfully; a value of 1 indicates that the stored procedure did not run successfully. The task stores the return code in a package variable, <b>ReturnCode</b>. </p>
            <p>Between the Execute SQL task and the Execute Process task is a precedence constraint. This precedence constraint allows the Execute Process task to run only if the following conditions are true:</p>
            <ul><li>
                The Execute SQL task finishes successfully.<br />
              </li><li>
                The <b>ReturnCode</b> variable contains a value of 0.<br />
              </li></ul>
          </td>
        </tr><tr>
          <td>
            <p>Execute Process task</p>
          </td>
          <td>
            <p>Runs the Expand utility, which decompresses Customers.cab.</p>
          </td>
        </tr><tr>
          <td>
            <p>Data Flow task</p>
          </td>
          <td>
            <p>Runs the data flow in the package.</p>
          </td>
        </tr><tr>
          <td>
            <p>OLE DB source</p>
          </td>
          <td>
            <p>Uses the decompressed Excel spreadsheet as the data source.</p>
          </td>
        </tr><tr>
          <td>
            <p>OLE DB destination </p>
          </td>
          <td>
            <p>Saves the data in the Excel spreadsheet to the destination table that was created by the Execute SQL task.</p>
          </td>
        </tr><tr>
          <td>
            <p>Execute SQL task</p>
          </td>
          <td>
            <p>Queries the <b>Execute_Process_Dest</b> table and stores the table rows, as a <b>Full result set</b> result set, in a variable of the <b>Object </b>data type. The name of the variable is <b>SQLReturn</b>.</p>
          </td>
        </tr><tr>
          <td>
            <p>Foreach Loop container</p>
          </td>
          <td>
            <p>Iterates through each table row stored in the <b>SQLReturn</b> variable and extracts column values into package variables that are mapped to the columns. To enumerate the table rows, the Foreach Loop container uses the Foreach ADO enumerator.</p>
          </td>
        </tr><tr>
          <td>
            <p>Script task</p>
          </td>
          <td>
            <p>Writes the values of the variables that are mapped to the <b>Execute_Process_Dest</b> columns to a text file.</p>
          </td>
        </tr><tr>
          <td>
            <p>Flat File connection manager</p>
          </td>
          <td>
            <p>Connects to the file to which the Script task writes the value of the variables. The name of this Flat File connection manager is <b>Check Query Results</b>.</p>
          </td>
        </tr><tr>
          <td>
            <p>File connection manager</p>
          </td>
          <td>
            <p>Connects to the file that contains the stored procedure that creates the <b>Execute_Process_Dest</b> table. The name of this File connection manager is <b>Create_Execute_Process_Dest.sql</b>.</p>
          </td>
        </tr><tr>
          <td>
            <p>OLE DB connection managers</p>
          </td>
          <td>
            <p>Connects to the Excel spreadsheet by using the Microsoft Jet 4.0 OLE DB provider. The name of this OLE DB connection manager is <b>customers</b>. </p>
            <p>Connects to the <b>AdventureWorks</b> database by using the SQL Server Native Client. The name of this OLE DB connection manager is <b>(local).AdventureWorks</b>.</p>
          </td>
        </tr></table>
    </content></div><h1 class="heading">Sample Results</h1><div id="sectionSection3" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">To see the execution results of the Execute Process sample package, open SQL Server Management Studio. In <b>Object Explorer</b>, connect to the server that contains <b>AdventureWorks</b>. In SQL Server Management Studio, click <b>New Query</b> and select <b>Database Engine Query</b>. In the database list, which defaults to the <b>master</b> database, select the <b>AdventureWorks</b> database, or type USE <b>AdventureWorks</b> in the query window and press <b>F5</b> to change the database.</p>
      <p xmlns="">Type and run the following Transact-SQL query:</p>
      <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>Select * from AdventureWorks.dbo.Execute_Process_Dest</pre></td></tr></table></span></div>
    </content></div><!--[if gte IE 5]>
			<tool:tip element="seeAlsoToolTip" avoidmouse="false"/><tool:tip element="languageFilterToolTip" avoidmouse="false"/><tool:tip element="roleInfoSpan" avoidmouse="false"/>
		<![endif]--></div>
      <div id="footer">
			
			© 2008 Microsoft Corporation. All rights reserved.
		</div>
    </div>
  </body>
</html>